<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ page import="jpa.*"%>
<%@ page import="pkg.*"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.text.*" %>
<%@ page import="java.io.*" %>
<%

String command = request.getParameter("type");
if(command == null) command = "";
IStatelessBean statelessBean = (IStatelessBean) session.getAttribute("statelessBean");

out.println("<root>");

try 
{
	String connectionURL = "jdbc:mysql://134.241.216.46:3306/bacnet"; 
	Connection connection = null; 
	Class.forName("com.mysql.jdbc.Driver").newInstance(); 
	connection = DriverManager.getConnection(connectionURL, "bacread", "20bac13read");
		 
	if(connection != null && !connection.isClosed())
	{
		Statement s = connection.createStatement();
		if(command.equals("Categories")) {
			ResultSet rs = s.executeQuery("SELECT * FROM CATEGORY");
			while(rs.next()) {
				out.println("<category>");
				out.println("<name>" + rs.getString("name")+ "</name>");
				out.println("</category>");
			}
		}
		else if(command.equals("DataItems")) {
			ResultSet rs = s.executeQuery("SELECT * FROM DATA_ITEM JOIN CATEGORY ON DATA_ITEM.categoryID = CATEGORY.categoryID JOIN UNIT ON DATA_ITEM.unitID = UNIT.unitID");
			while(rs.next()) {
				out.println("<dataitem>");
				out.println("<name>" + rs.getString("name")+ "</name>");
				out.println("<category>" + rs.getString("CATEGORY.name")+ "</category>");
				out.println("<units>" + rs.getString("UNIT.name")+ "</units>");
				out.println("</dataitem>");
			}
		}
		else if(command.equals("Data")) {
			DateFormat df = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
			DateFormat sqldf =  new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			java.util.Date startDate = df.parse(request.getParameter("startTime"));
			java.util.Date endDate = df.parse(request.getParameter("endTime"));
			out.println("<timescale>Time</timescale>");
			int numPoints = Integer.parseInt(request.getParameter("numPoints"));
			int dataItemIndex = 0;
			while(request.getParameter("dataItem" + dataItemIndex) != null) {
				String dataItem = request.getParameter("dataItem" + dataItemIndex);
				List<DataPoint> data = new ArrayList<DataPoint>();
				ResultSet rs = s.executeQuery("SELECT * FROM DATA JOIN DATA_ITEM ON DATA.dataitemID = DATA_ITEM.dataitemID WHERE DATA_ITEM.NAME = '" + dataItem + "' AND DATA.timestamp BETWEEN '" + sqldf.format(startDate) + "' AND '" + sqldf.format(endDate) + "'");
				while(rs.next()) {
					DataPoint dp = new DataPoint(rs.getTimestamp("timestamp"), rs.getDouble("value"));
					data.add(dp);
				}
				out.println("<dataitem>");
				out.println("<name>" + dataItem + "</name>");
				out.println("<datalist>");
				if(numPoints >= data.size()) {
					for(int i = 0; i < data.size(); i++) {
						out.println("<data>");
						out.println("<time>"+df.format(data.get(i).date)+"</time>");
						out.println("<value>"+data.get(i).value+"</value>");
						out.println("</data>");
					}
				}
				else {
					long target = (numPoints-1)*data.size();
					for(long index = 0; index <= target; index += data.size()) {
						int i = (int)((double)index/target*data.size());
						if(i >= data.size()) i = data.size()-1;
						out.println("<data>");
						out.println("<time>"+df.format(data.get(i).date)+"</time>");
						out.println("<value>"+data.get(i).value+"</value>");
						out.println("</data>");
					}
				}
				out.println("</datalist>");
				out.println("</dataitem>");
				dataItemIndex++;
			}
		}
	}
	if(connection != null) connection.close();
}
catch(Exception ex)
{
	out.println("Unable to connect to database.<br/>");
	ex.printStackTrace(new PrintWriter(out));
}
out.println("</root>");
%>
